package com.lisa.auto.util;

import com.alibaba.fastjson.JSONObject;
import com.lisa.auto.entity.ceramics.po.CeramicsGoodsPo;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.formula.functions.T;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author : WH
 * @group : tgb8
 * @Date : 2014-1-2 下午9:13:21
 * @Comments : 导入导出Excel工具类
 * @Version : 1.0.0
 */

public class JxlExcelUtils {

    public static final String DATA_KEY = "CERAMICS_DATA_";

    public static List<T> excelImport(String path, List<String> title) {
        List<T> list = new ArrayList<>();
        Workbook book = null;
        try {
            // 获取Excel对象
            book = book.getWorkbook(new File(path));
            // 获取Excel第一个选项卡对象
            Sheet sheet = book.getSheet(0);
            // 遍历选项卡，第一行是表头，所以索引数-1
            for (int i = 0; i < sheet.getRows() - 1; i++) {
                Cell cell = sheet.getCell(0, i + 1);
                String cellVal = cell.getContents().trim();
                if (title.contains(cellVal)){
                    System.out.println(i + "==========");
                }
            }
            // 返回导入的数据集合
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                book.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * @param in           ：承载着Excel的输入流
     * @param entityClass  ：List中对象的类型（Excel中的每一行都要转化为该类型的对象）
     * @param fieldMap     ：Excel中的中文列头和类的英文属性的对应关系Map
     *                     如　map.put("序号","id")
     * @param uniqueFields ：指定业务主键组合（即复合主键），这些列的组合不能重复
     *                     (如：excel中列名　"序号","名称" = String[] uniqueFields=new String[]{"序号","名称"})
     * @return ：List
     * @throws ExcelException
     * @MethodName : excelToList
     * @Description : 将Excel转化为List
     */
    public static <T> List<T> excelToList(
            InputStream in,
            String sheetName,
            Class<T> entityClass,
            LinkedHashMap<String, String> fieldMap,
            String[] uniqueFields
    ) throws ExcelException {
        Map<String, String> resultMap = new HashMap<>();
        //定义要返回的list
        List<T> resultList = new ArrayList<T>();
        try {
            //根据Excel数据源创建WorkBook
            Workbook wb = Workbook.getWorkbook(in);
            //获取工作表
            Sheet sheet = wb.getSheet(sheetName);
            //获取工作表的有效行数
            int realRows = sheet.getRows();
            //如果Excel中没有数据则提示错误
            if (realRows <= 1) {
                throw new ExcelException("Excel文件中没有任何数据");
            }

            int rowNum = 0;
            List<String> uniqueFieldList = Arrays.asList(uniqueFields);
            for (int i = 0; i < sheet.getRows(); i++) {
                Cell[] cells = sheet.getRow(i);
                for (int j = 0; j < cells.length; j++) {
                    String val = cells[j].getContents().toString().trim();
                    if (StringUtils.isBlank(val)){
                        continue;
                    }
                    if (uniqueFieldList.contains(val)){
                        rowNum = i;
                        break;
                    }
                }
            }
            Cell[] firstRow = sheet.getRow(rowNum);
            //将第一行数据转为字符串数组
            String[] excelFieldNames = new String[firstRow.length];

            //获取Excel中的列名
            for (int i = 0; i < firstRow.length; i++) {
                excelFieldNames[i] = firstRow[i].getContents().toString().trim();
            }
            //判断需要的字段在Excel中是否都存在
            boolean isExist = true;
            List<String> excelFieldList = Arrays.asList(excelFieldNames);

            for (String cnName : fieldMap.keySet()) {
                if (!excelFieldList.contains(cnName)) {
                    isExist = false;
                    break;
                }
            }

            //如果有列名不存在，则抛出异常，提示错误
            if (!isExist) {
                throw new ExcelException("Excel中缺少必要的字段，或字段名称有误");
            }
            //将列名和列号放入Map中,这样通过列名就可以拿到列号 (excelFieldNames,第一行数据-列名)
            LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
            for (int i = 0; i < excelFieldNames.length; i++) {
                colMap.put(excelFieldNames[i], firstRow[i].getColumn());
            }
            //判断是否有重复行
            //1.获取uniqueFields指定的列
            Cell[][] uniqueCells = new Cell[uniqueFields.length][];
            for (int i = 0; i < uniqueFields.length; i++) {
                int col = colMap.get(uniqueFields[i]);
                uniqueCells[i] = sheet.getColumn(col);
            }

            //2.从指定列中寻找重复行
            for (int i = 1; i < realRows; i++) {
                int nullCols = 0;
                for (int j = 0; j < uniqueFields.length; j++) {
                    String currentContent = uniqueCells[j][i].getContents();
                    Cell sameCell = sheet.findCell(currentContent,
                            uniqueCells[j][i].getColumn(),
                            uniqueCells[j][i].getRow() + 1,
                            uniqueCells[j][i].getColumn(),
                            uniqueCells[j][realRows - 1].getRow(),
                            true);
                    if (sameCell != null) {
                        nullCols++;
                    }
                }

                if (nullCols == uniqueFields.length) {
                    throw new ExcelException("Excel中有重复行，请检查");
                }
            }

            //将sheet转换为list
            for (int i = 1; i < realRows; i++) {
                //新建要转换的对象
                T entity = entityClass.newInstance();
                //给对象中的字段赋值
                for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
                    //获取中文字段名
                    String cnNormalName = entry.getKey();
                    //获取英文字段名
                    String enNormalName = entry.getValue();
                    //根据中文字段名获取列号
                    int col = colMap.get(cnNormalName);
                    //获取当前单元格中的内容
                    String content = sheet.getCell(col, i).getContents().toString().trim();
                    if (StringUtils.isBlank(content)){
                        continue;
                    }
                    //给对象赋值
                    setFieldValueByName(enNormalName, content, entity);
                }
                resultList.add(entity);
            }
        } catch (Exception e) {
            e.printStackTrace();
            //如果是ExcelException，则直接抛出
            if (e instanceof ExcelException) {
                throw (ExcelException) e;

                //否则将其它异常包装成ExcelException再抛出
            } else {
                e.printStackTrace();
                throw new ExcelException("导入Excel失败");
            }
        }
        return resultList;
    }

    /**
     * @param in           ：承载着Excel的输入流
     * @return ：List
     * @throws ExcelException
     * @MethodName : excelToList
     * @Description : 将Excel转化为List
     */
    public static String excelToListRows(
            InputStream in,
            String sheetName,
            String fieldName
    ) throws ExcelException {
        String fieldValue = "";
        try {
            //根据Excel数据源创建WorkBook
            Workbook wb = Workbook.getWorkbook(in);
            //获取工作表
            Sheet sheet = wb.getSheet(sheetName);
            //获取工作表的有效行数
            int realRows = sheet.getRows();
            //如果Excel中没有数据则提示错误
            if (realRows <= 1) {
                throw new ExcelException("Excel文件中没有任何数据");
            }
            for (int i = 0; i < sheet.getRows(); i++) {
                Cell[] cells = sheet.getRow(i);
                for (int j = 0; j < cells.length; j++) {
                    String val = cells[j].getContents().toString().trim();
                    if (StringUtils.isBlank(val)){
                        continue;
                    }
                    if (fieldName.equals(val)){
                        fieldValue = cells[j+1].getContents().toString().trim();
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            //如果是ExcelException，则直接抛出
            if (e instanceof ExcelException) {
                throw (ExcelException) e;
                //否则将其它异常包装成ExcelException再抛出
            } else {
                e.printStackTrace();
                throw new ExcelException("导入Excel失败");
            }
        }
        return fieldValue;
    }

    /**
     * @param fieldName  字段名
     * @param fieldValue 字段值
     * @param o          对象
     * @MethodName : setFieldValueByName
     * @Description : 根据字段名给对象的字段赋值
     */
    static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception {
        Field field = getFieldByName(fieldName, o.getClass());
        if (field != null) {
            field.setAccessible(true);
            //获取字段类型
            Class<?> fieldType = field.getType();
            //根据字段类型给字段赋值
            if (String.class == fieldType) {
                field.set(o, String.valueOf(fieldValue));
            } else if ((Integer.TYPE == fieldType)
                    || (Integer.class == fieldType)) {
                field.set(o, Integer.parseInt(fieldValue.toString()));
            } else if ((Long.TYPE == fieldType)
                    || (Long.class == fieldType)) {
                field.set(o, Long.valueOf(fieldValue.toString()));
            } else if ((Float.TYPE == fieldType)
                    || (Float.class == fieldType)) {
                field.set(o, Float.valueOf(fieldValue.toString()));
            } else if ((Short.TYPE == fieldType)
                    || (Short.class == fieldType)) {
                field.set(o, Short.valueOf(fieldValue.toString()));
            } else if ((Double.TYPE == fieldType)
                    || (Double.class == fieldType)) {
                field.set(o, Double.valueOf(fieldValue.toString()));
            } else if (Character.TYPE == fieldType) {
                if ((fieldValue != null) && (fieldValue.toString().length() > 0)) {
                    field.set(o, Character
                            .valueOf(fieldValue.toString().charAt(0)));
                }
            } else if (Date.class == fieldType) {
                field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString()));
            } else {
                field.set(o, fieldValue);
            }
        } else {
            throw new ExcelException(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
        }
    }

    /**
     * @param fieldName 字段名
     * @param clazz     包含该字段的类
     * @return 字段
     * @MethodName : getFieldByName
     * @Description : 根据字段名获取字段
     */
    static Field getFieldByName(String fieldName, Class<?> clazz) {
        //拿到本类的所有字段
        Field[] selfFields = clazz.getDeclaredFields();

        //如果本类中存在该字段，则返回
        for (Field field : selfFields) {
            if (field.getName().equals(fieldName)) {
                return field;
            }
        }
        //否则，查看父类中是否存在此字段，如果有则返回
        Class<?> superClazz = clazz.getSuperclass();
        if (superClazz != null && superClazz != Object.class) {
            return getFieldByName(fieldName, superClazz);
        }
        //如果本类和父类都没有，则返回空
        return null;
    }


    public static void main(String[] args) throws Exception{
        List<String> title = Arrays.asList(new String[]{"商品编号", "商品全名", "单位", "数量", "地址"});
        excelImport("D:\\中联物流\\城配系统\\陶瓷原始单\\兴发.xls", title);

        File f = new File("D:\\中联物流\\城配系统\\陶瓷原始单\\兴发.xls");
        InputStream input = null;
        input = new FileInputStream(f);
        LinkedHashMap<String, String> fieldMap = new LinkedHashMap();
        fieldMap.put("商品编号", "goodNum");
        fieldMap.put("商品全名", "goodName");
        fieldMap.put("单位", "company");
        fieldMap.put("数量", "quantity");
        String[] uniqueFields = new String[]{"商品编号", "商品全名", "单位", "数量"};
        List<CeramicsGoodsPo> ceramicsGoodsPos = excelToList(input, "导出数据", CeramicsGoodsPo.class, fieldMap, uniqueFields);
        System.out.println(JSONObject.toJSONString(ceramicsGoodsPos));
        InputStream input1 = new FileInputStream(f);
        System.out.println(excelToListRows(input1, "导出数据", "单位编号"));
    }

}